﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace DigDes.DSchool.SUPS.DataAccess.Database
{
    [System.ComponentModel.DataObject]
    public class DataRdr_Cargo
    {
        public static List<CargoLoader> GetTable(int startRowIndex, int maximumRows)
        {
            var connectionString = ConfigurationManager.ConnectionStrings[ConnectionStringKey].ConnectionString;
            using (var sqlConn = new SqlConnection(connectionString))
            {
                sqlConn.Open();
                var sqlCmd = sqlConn.CreateCommand();
                sqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
                sqlCmd.CommandText = "Select10RowsCargo";
                sqlCmd.Parameters.AddWithValue("@startRowIndex", startRowIndex);
                sqlCmd.Parameters.AddWithValue("@maximumRows", maximumRows);
                var reader = sqlCmd.ExecuteReader();
                List<CargoLoader> l = new List<CargoLoader>();
                while (reader.Read())
                {
                    l.Add(GetRow(reader));
                }
                return l;
            }
        }

        public static CargoLoader GetSelectedRow(string Cargo_ID)
        {
            var connectionString = ConfigurationManager.ConnectionStrings[ConnectionStringKey].ConnectionString;

            using (var sqlConn = new SqlConnection(connectionString))
            {
                using (var sqlCmd = sqlConn.CreateCommand())
                {
                    sqlCmd.CommandType = CommandType.StoredProcedure;
                    sqlCmd.CommandText = "SelectSpecificCargo";
                    if (Cargo_ID == null)
                        sqlCmd.Parameters.AddWithValue("@Cargo_ID", Int32.Parse("1"));
                    else
                        sqlCmd.Parameters.AddWithValue("@Cargo_ID", Int32.Parse(Cargo_ID));
                    CargoLoader cargo = new CargoLoader();
                    sqlConn.Open();
                    using (SqlDataReader reader = sqlCmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            cargo = GetRow(reader);
                        }
                    }
                    return cargo;
                }
            }
        }

        public int CountCargo(int startRowIndex, int maximumRows)
        {
            var connectionString = ConfigurationManager.ConnectionStrings[ConnectionStringKey].ConnectionString;
            using (var sqlConn = new SqlConnection(connectionString))
            {
                sqlConn.Open();
                var sqlCmd = sqlConn.CreateCommand();
                sqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
                sqlCmd.CommandText = "CountCargo";
                sqlCmd.ExecuteNonQuery();
                int count = System.Convert.ToInt32(sqlCmd.ExecuteScalar());
                sqlConn.Close();
                return count;
            }
        }

        public void DeleteSelectedCargo(int Cargo_ID, int original_Cargo_ID)
        {
            Cargo_ID = original_Cargo_ID;
            var connectionString = ConfigurationManager.ConnectionStrings[ConnectionStringKey].ConnectionString;

            using (var sqlConn = new SqlConnection(connectionString))
            {
                using (var sqlCmd = sqlConn.CreateCommand())
                {
                    sqlCmd.CommandType = CommandType.StoredProcedure;
                    sqlCmd.CommandText = "DeleteCargo";
                    sqlCmd.Parameters.AddWithValue("@Cargo_ID", Cargo_ID);
                    CargoLoader cargo = new CargoLoader();
                    sqlConn.Open();
                    sqlCmd.ExecuteNonQuery();
                    sqlConn.Close();

                }
            }
        }

        public void InsertRow(int Cargo_ID, string Name, string NameEng, string Description, string DescriptionEng, string ShortCode, string Code, string CodeGNG, string Mnemocode, string MnemocodeEng, bool IsUsed, bool IsEmpty)
        {
            var connectionString = ConfigurationManager.ConnectionStrings[ConnectionStringKey].ConnectionString;

            using (var sqlConn = new SqlConnection(connectionString))
            {
                using (var sqlCmd = sqlConn.CreateCommand())
                {
                    sqlCmd.CommandType = CommandType.StoredProcedure;
                    sqlCmd.CommandText = "InsertCargo";
                    sqlCmd.Parameters.AddWithValue("@Name", Name);
                    sqlCmd.Parameters.AddWithValue("@NameEng", NameEng);
                    sqlCmd.Parameters.AddWithValue("@Description", Description);
                    sqlCmd.Parameters.AddWithValue("@DescriptionEng", DescriptionEng);
                    sqlCmd.Parameters.AddWithValue("@ShortCode", ShortCode);
                    sqlCmd.Parameters.AddWithValue("@Code", Code);
                    sqlCmd.Parameters.AddWithValue("@CodeGNG", CodeGNG);
                    sqlCmd.Parameters.AddWithValue("@Mnemocode", Mnemocode);
                    sqlCmd.Parameters.AddWithValue("@MnemocodeEng", MnemocodeEng);
                    sqlCmd.Parameters.AddWithValue("@IsUsed", IsUsed);
                    sqlCmd.Parameters.AddWithValue("@IsEmpty", IsEmpty);

                    sqlConn.Open();
                    sqlCmd.ExecuteNonQuery();
                }
            }
        }

        //при добавлении почему то просит функцию с этими параметрами
        public void UpdateRow(string Cargo_ID, string Name, string NameEng, string Description, string DescriptionEng, string ShortCode, string Code, string CodeGNG, string Mnemocode, string MnemocodeEng, bool IsUsed, bool IsEmpty)
        {
            UpdateRow(Cargo_ID, Name, NameEng, Description, DescriptionEng, ShortCode, Code, CodeGNG, Mnemocode, MnemocodeEng, IsUsed, IsEmpty, Int32.Parse(Cargo_ID));
        }

        public void UpdateRow(string Cargo_ID, string Name, string NameEng, string Description, string DescriptionEng, string ShortCode, string Code, string CodeGNG, string Mnemocode, string MnemocodeEng, bool IsUsed, bool IsEmpty, int original_Cargo_ID)
        {
            var connectionString = ConfigurationManager.ConnectionStrings[ConnectionStringKey].ConnectionString;

            using (var sqlConn = new SqlConnection(connectionString))
            {
                using (var sqlCmd = sqlConn.CreateCommand())
                {
                    sqlCmd.CommandType = CommandType.StoredProcedure;
                    sqlCmd.CommandText = "UpdateCargo";
                    sqlCmd.Parameters.AddWithValue("@Name", Name);
                    sqlCmd.Parameters.AddWithValue("@NameEng", NameEng);
                    sqlCmd.Parameters.AddWithValue("@Description", Description);
                    sqlCmd.Parameters.AddWithValue("@DescriptionEng", DescriptionEng);
                    sqlCmd.Parameters.AddWithValue("@ShortCode", ShortCode);
                    sqlCmd.Parameters.AddWithValue("@Code", Code);
                    sqlCmd.Parameters.AddWithValue("@CodeGNG", CodeGNG);
                    sqlCmd.Parameters.AddWithValue("@Mnemocode", Mnemocode);
                    sqlCmd.Parameters.AddWithValue("@MnemocodeEng", MnemocodeEng);
                    sqlCmd.Parameters.AddWithValue("@IsUsed", IsUsed);
                    sqlCmd.Parameters.AddWithValue("@IsEmpty", IsEmpty);
                    sqlCmd.Parameters.AddWithValue("@Cargo_ID", Cargo_ID);

                    sqlConn.Open();
                    sqlCmd.ExecuteNonQuery();
                }
            }
        }

        public static void UploadImage(string Cargo_ID, byte[] Image)
        {
            var connectionString = ConfigurationManager.ConnectionStrings[ConnectionStringKey].ConnectionString;

            using (var sqlConn = new SqlConnection(connectionString))
            {
                using (var sqlCmd = sqlConn.CreateCommand())
                {
                    //sqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
                    //sqlCmd.CommandText = "InsertImageByID";
                    sqlCmd.CommandText = "UPDATE Cargo SET Image = @Image WHERE Cargo_ID = @Cargo_ID";
                    sqlCmd.Parameters.Add("@Image", SqlDbType.VarBinary).Value = Image;
                    sqlCmd.Parameters.AddWithValue("@Cargo_ID", Int32.Parse(Cargo_ID));

                    sqlConn.Open();
                    sqlCmd.ExecuteNonQuery();

                }
            }
        }

        public static byte[] LoadImage(int Cargo_ID)
        {
            string connectionString = ConfigurationManager.ConnectionStrings[ConnectionStringKey].ConnectionString;
            byte[] pict = null;
            using (SqlConnection sqlConn = new SqlConnection(connectionString))
            {
                using (SqlCommand sqlCmd = new SqlCommand("SELECT Image FROM Cargo WHERE Cargo_ID = @Cargo_ID", sqlConn))
                {
                    sqlCmd.CommandType = System.Data.CommandType.Text;


                    sqlCmd.Parameters.AddWithValue("@Cargo_ID", Cargo_ID);
                    sqlConn.Open();
                    try
                    {
                        pict = (byte[])sqlCmd.ExecuteScalar();
                    }
                    catch (Exception e) { Console.WriteLine(e.Message); }

                    sqlConn.Close();
                }
            }
            return pict;
        }

        public class CargoLoader
        {
            public Int32 Cargo_ID { get; set; }
            public string Name { get; set; }
            public string NameEng { get; set; }
            public string Description { get; set; }
            public string DescriptionEng { get; set; }
            public string ShortCode { get; set; }
            public string Code { get; set; }
            public string CodeGNG { get; set; }
            public string Mnemocode { get; set; }
            public string MnemocodeEng { get; set; }
            public bool IsUsed { get; set; }
            public bool IsEmpty { get; set; }
        }

        private static CargoLoader GetRow(SqlDataReader reader)
        {
            var res = new CargoLoader();
            res.Cargo_ID = reader.GetInt32(0);
            res.Name = GetString(1, reader);
            res.NameEng = GetString(2, reader);
            res.Description = GetString(3, reader);
            res.DescriptionEng = GetString(4, reader);
            res.ShortCode = GetString(5, reader);
            res.Code = GetString(6, reader);
            res.CodeGNG = GetString(7, reader);
            res.Mnemocode = GetString(8, reader);
            res.MnemocodeEng = GetString(9, reader);
            res.IsUsed = reader.GetBoolean(10);
            res.IsEmpty = reader.GetBoolean(11);
            
            return res;
        }

        private static IEnumerable<CargoLoader> GetSelectedRows(string storedProcedure)
        {
            {
                var connectionString = ConfigurationManager.ConnectionStrings[ConnectionStringKey].ConnectionString;
                using (var sqlConn = new SqlConnection(connectionString))
                {
                    sqlConn.Open();
                    var sqlCmd = sqlConn.CreateCommand();
                    sqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
                    sqlCmd.CommandText = storedProcedure;
                    var reader = sqlCmd.ExecuteReader();
                    List<CargoLoader> l = new List<CargoLoader>();
                    while (reader.Read())
                    {
                        yield return GetRow(reader);
                    }
                }
            }
        }

        private static readonly string ConnectionStringKey = "SUPSDB";

        /// <summary>
        /// Эта функция используется, для того что бы не вылетать на null   
        /// </summary>
        /// <param name="num"></param>
        /// <param name="reader"></param>
        /// <returns></returns>
        private static string GetString(int num, SqlDataReader reader)
        {
            string res = " ";
            try
            {
                res = reader.GetString(num);
            }
            catch
            {
                res = " ";
                return res;
            }
            return res;
        }
    }
}
